<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>query_checksum: common_schema documentation</title>
	<meta name="description" content="query_checksum: common_schema" />
	<meta name="keywords" content="query_checksum: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="query_checksum.html">query_checksum</a></h2>	
<h3>NAME</h3>
query_checksum(): checksum the result set of a query
<h3>TYPE</h3>
Procedure

<h3>DESCRIPTION</h3>
<p>
	Given a query, this procedure produces a deterministic checksum on the query's result set.
</p>
<p>
	The query is subject to the following limitations:
	<ul>
		<li>It must be a <strong>SELECT</strong></li>
		<li>It is limited to <strong>9</strong> columns</li>
		<li>Columns must be explicitly indicated; thus, the "star" form (e.g. <strong>SELECT * FROM ...</strong>) is not allowed</li>
		<li>Columns are limited to <strong>64K</strong> characters (values are translated as text)</li>
	</ul>
	Otherwise the query may produce any type of columns, use expressions, functions, etc. Resulting values may be <strong>NULL</strong>.
</p>
<p>
	The routine produces a checksum that is calculated via repetitive usage of the <strong>MD5</strong> algorithm. While the 
	operation is deterministic, it uses some internal heuristics (such as converting <strong>NULL</strong>s to <strong>'\0'</strong>
	so as to be able to process the <strong>MD5</strong> calculation). Knowing the internal heuristics it is possible to intentionally produce
	two different results sets which lead to same resulting checksum. The incidental appearance of such queries, though, is unlikely. 
	Moral is that this routine is useful in checking for data integrity in terms of possible errors, and is not suitable as a security
	threat elimination.
</p>
<p>
	The resulting checksum is also written to the <strong>@query_checksum_result</strong> session variable.
</p>


<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>query_checksum(in query TEXT CHARSET utf8) 
  READS SQL DATA</pre></blockquote>
</p>
<p>
  Input:
  <ul>
    <li><strong>query</strong>: query to execute; checksum run on result set
	</li>
  </ul>
</p>

<h3>EXAMPLES</h3>
<p>Checksum three queries. The first two return the exact same result:</p>
<blockquote><pre>mysql&gt; call query_checksum('select distinct n from (select cast(n/10 as unsigned) as n from numbers) s1 order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 314c86787aab14525759b29f81ac9664 |
+----------------------------------+

mysql&gt; call query_checksum('select n from (select cast(n/10 as unsigned) as n from numbers) s1 group by n order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| 314c86787aab14525759b29f81ac9664 |
+----------------------------------+

mysql&gt; call query_checksum('select distinct n+1 from (select cast(n/10 as unsigned) as n from numbers) s1 order by n');
+----------------------------------+
| checksum                         |
+----------------------------------+
| f4ea2e7f04d6edd28e9dd3e9419ec92c |
+----------------------------------+

mysql&gt; select @query_checksum_result;
+----------------------------------+
| @query_checksum_result           |
+----------------------------------+
| f4ea2e7f04d6edd28e9dd3e9419ec92c |
+----------------------------------+
</pre></blockquote>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="crc64.html">crc64()</a>,
<a href="exec.html">exec()</a>,
<a href="random_hash.html">random_hash()</a>

<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
